﻿Imports System.Data.OleDb

Public Class MonHocDAO
    Dim sDBName As String = "Database\thietkethoikhoabieu.mdb"

    Public Function LayDanhSachMonHoc() As List(Of MonHocDTO)
        Dim str = "SELECT * FROM MonHoc"
        Dim conn = DataProvider.ConnectDB(sDBName)
        Dim cmd As New OleDbCommand(str, conn)
        Dim reader = cmd.ExecuteReader()
        Dim ds As New List(Of MonHocDTO)

        While reader.Read()
            Dim mh As New MonHocDTO
            mh.MaMonHoc = reader.GetString(0)
            mh.TenMonHoc = reader.GetString(1)
            ds.Add(mh)
        End While

        conn.Close()
        Return ds
    End Function

    Public Function ThemMonHoc(ByVal mh As MonHocDTO) As Integer
        Dim str = String.Format("INSERT INTO MonHoc(MaMonHoc, TenMonHoc) " _
                & "values ('{0}','{1}')", mh.MaMonHoc, mh.TenMonHoc)
        Dim conn = DataProvider.ConnectDB(sDBName)
        Dim cmd As New OleDbCommand(str, conn)
        Dim ketqua = -1
        ketqua = cmd.ExecuteNonQuery()
        conn.Close()

        Return ketqua
    End Function

    Public Function XoaMonHoc(ByVal mh As MonHocDTO) As Integer
        Dim str = "DELETE FROM MonHoc WHERE MaMonHoc='" & mh.MaMonHoc & "'"
        Dim conn = DataProvider.ConnectDB(sDBName)
        Dim cmd As New OleDbCommand(str, conn)
        Dim ketqua = -1
        ketqua = cmd.ExecuteNonQuery()

        conn.Close()
        Return ketqua
    End Function

    Public Function SuaMonHoc(ByVal mh As MonHocDTO) As Integer
        Dim str = "UPDATE MonHoc SET TenMonHoc='" & mh.TenMonHoc & "' WHERE MaMonHoc='" & mh.MaMonHoc & "'"
        Dim conn = DataProvider.ConnectDB(sDBName)
        Dim cmd As New OleDbCommand(str, conn)
        Dim ketqua = -1
        ketqua = cmd.ExecuteNonQuery()

        conn.Close()
        Return ketqua
    End Function

    Public Function TraCuuMonHoc(ByVal mh As MonHocDTO) As List(Of MonHocDTO)
        Dim sSelect = String.Format("SELECT * FROM MonHoc WHERE MaMonHoc LIKE '%{0}%'" _
                    & " AND TenMonHoc LIKE '%{1}%'", mh.MaMonHoc, mh.TenMonHoc)

        Dim conn = DataProvider.ConnectDB(sDBName)
        Dim cmd As New OleDbCommand(sSelect, conn)
        Dim reader = cmd.ExecuteReader()
        Dim ds As New List(Of MonHocDTO)

        While reader.Read()
            Dim rmh As New MonHocDTO
            rmh.MaMonHoc = reader.GetString(0)
            rmh.TenMonHoc = reader.GetString(1)

            ds.Add(rmh)
        End While

        conn.Close()
        Return ds

    End Function
End Class
